#include <QDir>
#include <QFile>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlRecord>
#include <QVariant>
#include <QMessageBox>

#include "archdata.h"

void initArchData()
{
  QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
  QDir curPath;
  if(!curPath.exists("archdata.db")){
    db.setDatabaseName("archdata.db");
    db.open(); 
    QSqlQuery query;
    query.exec("create table archive (num varchar(40) primary key, "
	       "sender varchar(40), createTime varchar(30))");
    query.exec("create table video (filename varchar(40) primary key, "
	       "archNum varchar(40), startTime double, endTime double,duration double,"
	       "temperature double, recipe varchar(100),"
	       "createTime varchar(30))");
    query.exec("create table image (filename varchar(40) primary key,"
	       "videoFile varchar(40), createTime varchar(30), shotTime double)");
  }
  else{
    db.setDatabaseName("archdata.db");
    db.open();
  }
}

int getArchs(QStringList &sl, QString num)
{
  QSqlQuery query;
  QString str ="select num from archive where num like '";
  str += num +"%'";
  query.exec(str);

  sl.clear(); 
  while(query.next()){
    sl << query.value(0).toString();
  }
  return sl.size();
}

int getArchs(QStringList &sl)
{
  QSqlQuery query;
  QString str ="select num from archive";
  query.exec(str);

  sl.clear(); 
  while(query.next()){
    sl << query.value(0).toString();
  }
  return sl.size();
}

int getArchs(QStringList& sl, QString sortF, QString type, QString likeF, QString likeV)
{
  QSqlQuery query;
  QString str ="select num from archive where %1 like '";
  QString str2="' order by %1 %2";
  str = str.arg(likeF);
  str2 = str2.arg(sortF).arg(type);
  str += likeV + str2;
  str = str.arg(likeF).arg(likeV).arg(sortF).arg(type);
  query.exec(str);

  sl.clear(); 
  while(query.next()){
    sl << query.value(0).toString();
  }
  return 1;
}

int getVideos(QString num, QStringList& sl, QString sortF, QString type, QString likeF, QString likeV)
{
  QSqlQuery query;
  QString str ="select filename from video where (archNum = '%1') and (%2 like '";
  QString str2="') order by %1 %2";
  str = str.arg(num).arg(likeF);
  str2 = str2.arg(sortF).arg(type);
  str += likeV + str2;
  query.exec(str);

  sl.clear(); 
  while(query.next()){
    sl << query.value(0).toString();
  }
  return 1;
}

int getImages(QString num, QStringList& sl,QString sortF, QString type, QString likeF, QString likeV)
{
  QSqlQuery query;
  QString str ="select image.filename from video, image where (video.filename = image.videoFile) and (video.archNum = '%1') and (image.%2 like '";
  QString str2="') order by image.%1 %2";
  str = str.arg(num).arg(likeF);
  str2 = str2.arg(sortF).arg(type);
  str += likeV + str2;
  query.exec(str);

  sl.clear(); 
  while(query.next()){
    sl << query.value(0).toString();
  }
  return 1;
}

int getVideoRecord(QString filename, VideoRecord& videoRecord)
{
  QSqlQuery query;
  QString str ="select * from video where filename = '";
  str += filename +"'";
  query.exec(str);

  if(query.next()){
    videoRecord.filename = query.value(0).toString();
    videoRecord.archNum = query.value(1).toString();
    videoRecord.startTime = query.value(2).toString().toDouble();
    videoRecord.endTime = query.value(3).toString().toDouble();
    videoRecord.duration = videoRecord.endTime - videoRecord.startTime;
    videoRecord.temperature = query.value(4).toString().toDouble();
    videoRecord.recipe = query.value(5).toString();
    videoRecord.createTime = query.value(6).toString();
    return 1;
  }
  return 0;
}

int addVideoRecord(VideoRecord& videoRecord)
{
  QSqlQuery query;
  QString str ="select * from video where filename = '";
  str += videoRecord.filename+"'";
  query.exec(str);
  if(query.next())return 0;
  str = "insert into video values('%1', '%2', %3, %4, %5, %6, '%7', '%8')";
  str = str.arg(videoRecord.filename).
    arg(videoRecord.archNum).
    arg(videoRecord.startTime).
    arg(videoRecord.endTime).
    arg(videoRecord.duration).
    arg(videoRecord.temperature).
    arg(videoRecord.recipe).
    arg(videoRecord.createTime);
  query.exec(str);
  return 1;
}

int deleteVideoRecord(QString filename)
{
  QSqlQuery query;
  QString str;

  str = "select filename from image where videoFile = '";
  str += filename + "'";
  query.exec(str);
  while(query.next()){
    str = query.value(0).toString();
    deleteImageRecord(str);
  } 

  str = "delete from video where filename = '";
  str += filename +"'";
  query.exec(str);

  str = "videos/";
  str += filename;
  QFile::remove(str);
  return 1;  
}

int getArchRecord(QString archNum, ArchRecord& archRecord)
{
  QSqlQuery query;
  QString str ="select * from archive where num = '";

  str += archNum + "'";
  query.exec(str);
 
  if(query.next()){
    archRecord.num = query.value(0).toString();
    archRecord.sender = query.value(1).toString();
    archRecord.createTime = query.value(2).toString();
    
    return 1;
  }
return 0;
}

int addArchRecord(ArchRecord& archRecord)
{
  QSqlQuery query;
  QString str ="select * from archive where num = '";
  str += archRecord.num+"'";
  query.exec(str);
  if(query.next())return 0;
  str = "insert into archive values('%1', '%2', '%3')";
  str=str.arg(archRecord.num).
    arg(archRecord.sender).
    arg(archRecord.createTime);
  query.exec(str);
  return 1;
}

int deleteArchRecord(QString archNum)
{
  QSqlQuery query;
  QString str = "select * from video where archNum = '";
  str += archNum + "'";
  query.exec(str);
  if(query.next())return 0;
 
  str = "delete from archive where num = '";
  str += archNum +"'";
  query.exec(str);
  return 1;  
}

int getImageRecord(QString filename, ImageRecord& imageRecord)
{
  QSqlQuery query;
  QString str ="select * from image where filename = '";
  str +=filename+"'";
  query.exec(str);
  if(query.next()){
    imageRecord.filename = query.value(0).toString();
    imageRecord.videoFile = query.value(1).toString();
    imageRecord.createTime = query.value(2).toString();
    imageRecord.shotTime = query.value(3).toDouble();
  
    return 1;
  }
  return 0;
}

int addImageRecord(ImageRecord& imageRecord)
{
  QSqlQuery query;
  QString str ="select * from image where filename = '";
  str += imageRecord.filename+"'";
  query.exec(str);
  if(query.next())return 0;
  str = "insert into image values('%1', '%2', '%3', %4)";
  str=str.arg(imageRecord.filename).
    arg(imageRecord.videoFile).
    arg(imageRecord.createTime).
    arg(imageRecord.shotTime);
  query.exec(str);
  return 1;
}


int deleteImageRecord(QString filename)
{
  QSqlQuery query;
  QString str = "delete from image where filename = '";
  str += filename +"'";
  query.exec(str);
  str = "images/";
  str += filename;
  QFile::remove(str);
  return 1;  
}

void modifyRecord(QString tableName, QString fieldName, QString fieldValue, QString keyName,QString keyValue)
{
  QSqlQuery query;
  QString str;

  str = QString("update %1 set %2 = '%3' where %4 = '%5'");
  str = str.arg(tableName)
    .arg(fieldName).arg(fieldValue)
    .arg(keyName).arg(keyValue);
  query.exec(str);
}

void getTableField(QString table,QStringList& fl)
{
  QSqlQuery query;
  QSqlRecord record;
  QString str = "select * from %1";
  int i,n;
  str = str.arg(table);
  query.exec(str);

  fl.clear();
  //if(query.next()){
    record = query.record();
    n = record.count();
    for(i=0;i<n;i++) fl <<record.fieldName(i);
    //}
}

int getTableFieldValue(QString table, QString type, QString value, QStringList& fl, QStringList& vl)
{
  QSqlQuery query;
  QSqlRecord record;
  int i,n;

  QString str = "select * from %1 where %2 = '%3'";
  str = str.arg(table).arg(type).arg(value);
  query.exec(str);
  fl.clear();
  vl.clear();

  if(query.next()){
    record = query.record();
    n=record.count();
    for(i=0;i<n;i++){
      fl<<record.fieldName(i);
      vl<<query.value(i).toString();
    }
    return 1;
  }
  return 0;
}

int getVideoMark(QString filename, double& start, double& end)
{
  QSqlQuery query;
  QString str = "select startTime, endTime from video where filename = '";
  str += filename +"'";
  query.exec(str);

  if(query.next()){
    start = query.value(0).toString().toDouble();
    end = query.value(1).toString().toDouble();
    return 1;
  }
  return 0;
}
